package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.LinkedList;

import util.Number;

import model.Item;
import model.ItemSupplier;
import model.ItemType;
import model.ItemTypeMeasurement;
import model.Outsider;

public class DBItem implements IFItem
{
	private Connection con;

	public DBItem()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createItem(Item item) throws SQLException
	{
		int rc = -1;

		String query = "SELECT item_id FROM item WHERE item_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createItem", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, item.getName());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO item(item_name, item_stock, item_price, item_type_id, item_dimensions) VALUES (?, ?, ?, ?, ?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createItem", query);

				try 
				{
					ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

					ps.setString(1, item.getName());
					ps.setInt(2, item.getStock());
					ps.setDouble(3, item.getPrice());
					ps.setInt(4, item.getType().getTypeID());
					ps.setString(5, item.getDimensions());

					rc = ps.executeUpdate();
					ResultSet insertedRow = ps.getGeneratedKeys();

					if ((rc > 0) && insertedRow.next())
					{
						query = "INSERT INTO item_supplier(is_item_id, is_outsider_id, is_item_quantity, is_outsider_active) VALUES(?, ?, ?, 1)";

						if (DBConnection.doDebug())
							DBConnection.printDebug("createItem", query);

						ps = con.prepareStatement(query);

						for(ItemSupplier supplier : item.getSuppliers())
						{
							ps.setInt(1, insertedRow.getInt(1));
							ps.setInt(2, supplier.getOutsider().getOutsiderID());
							ps.setInt(3, supplier.getQuantity());

							ps.addBatch();
						}

						ps.executeBatch();

						rc = insertedRow.getInt(1);
					}
				}
				catch (SQLException e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createItem - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createItem - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateItem(Item item) throws SQLException
	{
		int rc = -1;

		String query = "UPDATE item SET item_name = ?, item_stock = ?, item_price = ?, item_type_id = ?, item_dimensions = ? WHERE item_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateItem", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, item.getName());
			ps.setInt(2, item.getStock());
			ps.setDouble(3, item.getPrice());
			ps.setInt(4, item.getType().getTypeID());
			ps.setString(5, item.getDimensions());
			ps.setInt(6, item.getItemID());

			rc = ps.executeUpdate();

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateItem - UPDATE: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateItemQuantity(int itemID, int addStock, int removeStock) throws SQLException
	{
		int stock = 0;
		int result = -1;

		String query = "SELECT item_stock FROM item WHERE item_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateItemQuantity", query);

		try	{
			ResultSet rs;

			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);

			rs = ps.executeQuery();

			if (rs.next())
			{
				stock = rs.getInt("item_stock") + addStock - removeStock;

				try {
					query = "UPDATE item SET item_stock = ? WHERE item_id = ?";

					ps = con.prepareStatement(query);

					ps.setInt(1, stock);
					ps.setInt(2, itemID);

					result = ps.executeUpdate();
				}
				catch (SQLException e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updateItemQuantity - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch(Exception e)	
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateItemQuantity - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return result;
	}

	@Override
	public int addItemStock(int itemID, int quantity, Outsider supplier, boolean update) throws SQLException
	{
		int result = -1;
		String query;

		if (update)
			query = "UPDATE item_supplier SET is_item_quantity = is_item_quantity + ? WHERE is_item_id = ? AND is_outsider_id = ?";
		else
			query = "INSERT INTO item_supplier(is_item_quantity, is_item_id, is_outsider_id, is_outsider_active) VALUES(?, ?, ?, 1)";

		if (DBConnection.doDebug())
			DBConnection.printDebug("addItemStock", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, quantity);
			ps.setInt(2, itemID);
			ps.setInt(3, supplier.getOutsiderID());

			result = ps.executeUpdate();

			if (result > 0)
				result = updateItemQuantity(itemID, quantity, 0);

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - addItemStock - UPDATE: " + e);
				e.printStackTrace();
			}
		}

		return result;
	}

	@Override
	public int getItemStock(int itemID) throws SQLException
	{
		int quantity = -1;

		String query = "SELECT item_stock FROM item WHERE item_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getItemStock", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);

			ResultSet results = ps.executeQuery();

			if (results.next())
				quantity = results.getInt("item_stock");

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getItemStock - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return quantity;
	}

	@Override
	public double getItemPrice(int itemID) throws SQLException
	{
		double price = 0.0;

		String query = "SELECT item_price FROM item WHERE item_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getItemPrice", query);

		try
		{
			ResultSet results;

			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);

			results = ps.executeQuery();

			if (results.next())
				price = results.getDouble("item_price");

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getItemPrice - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return price;
	}

	@Override
	public Item searchItemByID(int id) throws SQLException
	{
		ResultSet results;
		Item newItem = null;

		String query = "SELECT i.*, it.it_name, im.im_measurement FROM item AS i LEFT JOIN item_type AS it ON i.item_type_id = it.it_id LEFT JOIN item_measurement AS im ON it.it_measurement_id = im.im_id WHERE i.item_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchItemByID", query);

		try 
		{	
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, id);

			results = ps.executeQuery();

			if(results.next())
			{
				newItem = createItemObject(results);
			}

			ps.close();     
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchItemByID - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newItem;
	}

	@Override
	public Item searchItemByName(String name) throws SQLException
	{
		Item newItem = null;

		String query = "SELECT item_id, item_stock FROM item WHERE item_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchItemByName", query);

		try 
		{	
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, name);

			ResultSet results = ps.executeQuery();

			if(results.next())
			{
				newItem = new Item();

				newItem.setItemID(results.getInt("item_id"));
				newItem.setStock(results.getInt("item_stock"));
			}

			ps.close();     
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchItemByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newItem;
	}

	@Override
	public LinkedList<Item> printAllItemsWithStock() throws SQLException
	{
		LinkedList<Item> itemList = new LinkedList<Item>();

		String query = "SELECT item_name FROM item WHERE item_stock > 0 ORDER BY item_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllItemsWithStock", query);

		try 
		{	
			Statement st = con.createStatement();

			ResultSet results = st.executeQuery(query);

			while(results.next())
			{
				Item newItem = new Item();

				newItem.setName(results.getString("item_name"));

				itemList.add(newItem);
			}

			st.close();     
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - printAllItemsWithStock - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return itemList;
	}

	@Override
	public LinkedList<Item> searchItemEverything(boolean active, String type, String search) throws SQLException
	{
		boolean number = Number.isNumber(search);

		return searchItems(active, type, search, "item_name LIKE ? OR o.outsider_name LIKE ? OR c.city_name LIKE ? OR cn.country_name LIKE ? OR it.it_name LIKE ? OR im.im_measurement LIKE ?" + (number ? " OR item_id = ?" : ""), (number ? 7 : 6));
	}

	@Override
	public LinkedList<Item> searchItemName(boolean active, String type,	String search) throws SQLException
	{
		return searchItems(active, type, search, "item_name LIKE ?", 1);
	}

	@Override
	public LinkedList<Item> searchItemSupplier(boolean active, String type,	String search) throws SQLException
	{
		return searchItems(active, type, search, "o.outsider_name LIKE ?", 1);
	}

	@Override
	public LinkedList<Item> searchItemCity(boolean active, String type,	String search) throws SQLException
	{
		return searchItems(active, type, search, "c.city_name LIKE ?", 1);
	}

	@Override
	public LinkedList<Item> searchItemCountry(boolean active, String type, String search) throws SQLException
	{
		return searchItems(active, type, search, "cn.country_name LIKE ?", 1);
	}

	/**
	 * Our method for handling item search
	 * 
	 * @param 	boolean		are we searching for active only or not
	 * @param 	String		the item type we are searching for, can be empty
	 * @param	String 		our search word, we use it for the prepared statement
	 * @param 	String		our extra query (in order to keep everything in one method and not 500 lines of code..)
	 * @param 	int			might be kinda hardcoded, number of prepared statements we have to do, most (only) used in searchItemEverything
	 * 
	 * @return  LinkedList<Item>
	 */
	private LinkedList<Item> searchItems(boolean active, String itemType, String search, String clause, int num) throws SQLException
	{
		LinkedList<Item> listItems = new LinkedList<Item>();

		String query = "SELECT item.*, it.it_name, im.im_measurement FROM item LEFT JOIN item_type AS it ON item.item_type_id = it.it_id LEFT JOIN item_measurement AS im ON it.it_measurement_id = im.im_id WHERE item_id IN (SELECT DISTINCT i.item_id FROM item AS i LEFT JOIN item_supplier AS isp ON i.item_id = isp.is_item_id LEFT JOIN outsider AS o ON isp.is_outsider_id = o.outsider_id LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id WHERE (" + clause + ")" + (itemType.length() > 0 ? " AND it.it_name = '" + itemType + "'" : "") + (active ? " AND item_stock > 0" : "") + ")";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchItems", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (clause.length() > 0)
			{
				ps.setString(1, "%" + search + "%");

				if (num > 1)
				{
					for(int i = 2; i <= num; i++)
						ps.setString(i, (i == 7 ? search : "%" + search + "%"));						
				}
			}

			ResultSet results = ps.executeQuery();

			while (results.next())
			{
				Item newItem = createItemObject(results);

				listItems.add(newItem);
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchItems - SELECT: " + e);
				e.printStackTrace();
			}

			return null;
		}

		return listItems;
	}

	private Item createItemObject(ResultSet results) throws SQLException
	{
		Item newItem = new Item();

		newItem.setItemID(results.getInt("item_id"));
		newItem.setName(results.getString("item_name"));
		newItem.setPrice(results.getDouble("item_price"));
		newItem.setStock(results.getInt("item_stock"));
		newItem.setDimensions(results.getString("item_dimensions"));
		newItem.setType(new ItemType(results.getInt("item_type_id"), results.getString("it_name"), new ItemTypeMeasurement(results.getString("im_measurement"))));

		return newItem;
	}
}